﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Common.DBUtility
{
   public class DbHelper
   {
      private readonly Database _database;

      internal DbHelper(Database db)
      {
         _database = db;
      }

      #region Public Mathod

      /// <summary>
      /// Add In Parameter to DBCommand
      /// </summary>
      /// <param name="command">DBCommand</param>
      /// <param name="name">the name of the parameter</param>
      /// <param name="dbType">the type of the parameter</param>
      public void AddInParameter(DbCommand command, string name, DbType dbType)
      {
         _database.AddInParameter(command, name, dbType);
      }

      /// <summary>
      /// Add In Parameter to DBCommand with value
      /// </summary>
      /// <param name="command">DBCommand</param>
      /// <param name="name">the name of the parameter</param>
      /// <param name="dbType">the type of the parameter</param>
      /// <param name="value">the value of the parameter</param>
      public void AddInParameter(DbCommand command, string name, DbType dbType, object value)
      {
         _database.AddInParameter(command, name, dbType, value);
      }

      /// <summary>
      /// Add In Parameter to DBCommand with value
      /// </summary>
      /// <param name="command">DBCommand</param>
      /// <param name="name">the name of the parameter</param>
      /// <param name="dbType">the type of the parameter</param>
      /// <param name="value">the value of the parameter</param>
      public void AddInParameter(DbCommand command, string name, object value)
      {
         _database.AddInParameter(command, name, GetDbType(value), value);
      }

      /// <summary>
      /// Add out Parameter to DBCommand
      /// </summary>
      /// <param name="command">DBCommand</param>
      /// <param name="name">the name of the parameter</param>
      /// <param name="dbType">the type of the parameter</param>
      /// <param name="size">the size of the parameter</param>
      public void AddOutParameter(DbCommand command, string name, DbType dbType, int size)
      {
         _database.AddOutParameter(command, name, dbType, size);
      }


      public virtual DataSet ExecuteDataSet(DbCommand command)
      {
         return _database.ExecuteDataSet(command);
      }


      public virtual DataSet ExecuteDataSet(CommandType commandType, string commandText)
      {
         return _database.ExecuteDataSet(commandType, MapSql(commandText));
      }

      public virtual DataSet ExecuteDataSet(string storedProcedureName, params object[] parameterValues)
      {
         return _database.ExecuteDataSet(MapSql(storedProcedureName), parameterValues);
      }

      public virtual DataSet ExecuteDataSet(string sql, IDictionary<string, object> parameterList)
      {
         return ExecuteDataSet(GetSqlStringCommand(sql, parameterList));
      }


      public virtual DataSet ExecuteDataSet(string sql)
      {
         return ExecuteDataSet(CommandType.Text, sql);
      }


      public virtual int ExecuteNonQuery(DbCommand command)
      {
         return _database.ExecuteNonQuery(command);
      }

      public virtual int ExecuteNonQuery(string commandText)
      {
         return _database.ExecuteNonQuery(CommandType.Text, MapSql(commandText));
      }

      public virtual int ExecuteNonQuery(string commandText, IDictionary<string, object> parameterList)
      {
         return ExecuteNonQuery(GetSqlStringCommand(commandText, parameterList));
      }

      public virtual int ExecuteNonQuery(string storedProcedureName, params object[] parameterValues)
      {
         return _database.ExecuteNonQuery(MapSql(storedProcedureName), parameterValues);
      }


      public virtual int ExecuteInsert(DbCommand command)
      {
         command.CommandText += ";SELECT @@IDENTITY;";
         return Convert.ToInt32(_database.ExecuteScalar(command));
      }

      public virtual int ExecuteInsert(string commandText)
      {
         commandText += ";SELECT @@IDENTITY;";
         return Convert.ToInt32(_database.ExecuteScalar(CommandType.Text, commandText));
      }

      public virtual int ExecuteInsert(string commandText, IDictionary<string, object> parameterList)
      {
         return ExecuteInsert(GetSqlStringCommand(commandText, parameterList));
      }


      public virtual IDataReader ExecuteReader(DbCommand command)
      {
         return _database.ExecuteReader(command);
      }

      public virtual IDataReader ExecuteReader(CommandType commandType, string commandText)
      {
         return _database.ExecuteReader(commandType, MapSql(commandText));
      }

      public virtual IDataReader ExecuteReader(string storedProcedureName, params object[] parameterValues)
      {
         return _database.ExecuteReader(MapSql(storedProcedureName), parameterValues);
      }

      public virtual IDataReader ExecuteReader(string sql)
      {
         return ExecuteReader(CommandType.Text, sql);
      }

      public virtual IDataReader ExecuteReader(string commandText, IDictionary<string, object> parameterList)
      {
         return ExecuteReader(GetSqlStringCommand(commandText, parameterList));
      }


      public virtual object ExecuteScalar(DbCommand command)
      {
         return _database.ExecuteScalar(command);
      }

      public virtual T ExecuteScalar<T>(DbCommand command)
      {
         T result = default(T);
         try
         {
            object resultObj = ExecuteScalar(command);
            if (resultObj == null)
               return result;
            return (T) resultObj;
         }
         catch (InvalidCastException icx)
         {
            throw icx;
         }
      }

      public virtual object ExecuteScalar(string commandText, IDictionary<string, object> parameterList)
      {
         return ExecuteScalar(GetSqlStringCommand(commandText, parameterList));
      }

      public virtual T ExecuteScalar<T>(string commandText, IDictionary<string, object> parameterList)
      {
         T result = default(T);
         try
         {
            object resultObj = ExecuteScalar(GetSqlStringCommand(commandText, parameterList));
            if (resultObj == null)
               return result;
            return (T) resultObj;
         }
         catch (InvalidCastException icx)
         {
            throw icx;
         }
      }


      public virtual object ExecuteScalar(CommandType commandType, string commandText)
      {
         return _database.ExecuteScalar(commandType, MapSql(commandText));
      }


      public virtual object ExecuteScalar(string storedProcedureName, params object[] parameterValues)
      {
         return _database.ExecuteScalar(MapSql(storedProcedureName), parameterValues);
      }


      public virtual object ExecuteScalar(string sql)
      {
         return ExecuteScalar(CommandType.Text, sql);
      }

      public virtual T ExecuteScalar<T>(string sql)
      {
         T result = default(T);
         try
         {
            object resultObj = ExecuteScalar(CommandType.Text, sql);
            if (resultObj == null)
               return result;
            return (T) resultObj;
         }
         catch (InvalidCastException icx)
         {
            throw icx;
         }
      }


      public DataSet GetPagedDataSet(string cmdText, int pageIndex, int pageSize, string orderBy,
                                     IDictionary<string, object> parameterList, out int totalRecord)
      {
         var result = new DataSet();
         totalRecord = 0;
         LoadDataSet(PageHelper.GeneratePagingSql(cmdText, pageIndex, pageSize, GetOrderByList(orderBy))
                     , parameterList
                     , result
                     , new[] {"ResultDataSet", "TotalRecord"});
         if (result.Tables["ResultDataSet"].Rows.Count > 0)
            totalRecord = (int) result.Tables["TotalRecord"].Rows[0][0];
         return result;
      }

      //TODO:GetOrderByList
      private string GetOrderByList(string orderBy)
      {
         string result = string.Empty;
         string[] orderByList = orderBy.Split(new[] {','}, StringSplitOptions.RemoveEmptyEntries);
         foreach (string orderEntry in orderByList)
         {
            string orderField = orderEntry.Split(new[] {' '}, StringSplitOptions.RemoveEmptyEntries)[0];
            if (orderEntry.IndexOf(" ASC", StringComparison.InvariantCultureIgnoreCase) <= 0
                && orderEntry.IndexOf(" DESC", StringComparison.InvariantCultureIgnoreCase) <= 0)
            {
               result += orderField + " ASC,";
               continue;
            }
            if (orderEntry.IndexOf(" ASC", StringComparison.InvariantCultureIgnoreCase) > 0)
            {
               result += orderField + " ASC,";
               continue;
            }
            if (orderEntry.IndexOf(" DESC", StringComparison.InvariantCultureIgnoreCase) > 0)
            {
               result += orderField + " DESC,";
               continue;
            }
         }
         if (result.Length > 0) return result.Substring(0, result.Length - 1);
         return result;
      }


      public DataSet GetPagedDataSet(string cmdText, int pageIndex, int pageSize, string orderBy, out int totalRecord)
      {
         var result = new DataSet();
         totalRecord = 0;
         LoadDataSet(PageHelper.GeneratePagingSql(cmdText, pageIndex, pageSize, orderBy)
                     , null
                     , result
                     , new[] {"ResultDataSet", "TotalRecord"});
         if (result.Tables["ResultDataSet"].Rows.Count > 0)
            totalRecord = (int) result.Tables["TotalRecord"].Rows[0][0];
         return result;
      }


      public virtual void LoadDataSet(DbCommand command, DataSet dataSet, string[] tableNames)
      {
         _database.LoadDataSet(command, dataSet, tableNames);
      }

      public void LoadDataSet(DbCommand command, DataSet dataSet, string tableName)
      {
         LoadDataSet(command, dataSet, new[] {tableName});
      }

      public void LoadDataSet(string commandText, IDictionary<string, object> parameterList, DataSet dataSet,
                              string[] tableNames)
      {
         LoadDataSet(GetSqlStringCommand(commandText, parameterList), dataSet, tableNames);
      }

      public void LoadDataSet(string commandText, DataSet dataSet, string[] tableNames)
      {
         LoadDataSet(GetSqlStringCommand(commandText, null), dataSet, tableNames);
      }


      public object GetParameterValue(DbCommand command, string name)
      {
         return _database.GetParameterValue(command, name);
      }

      /// <summary>
      /// GetCommandBy SqlQuery
      /// </summary>
      /// <param name="query">Sql Query</param>
      /// <returns>DbCommand</returns>
      public DbCommand GetSqlStringCommand(string query)
      {
         return GetSqlStringCommand(query, null);
      }

      /// <summary>
      /// GetCommandBy SqlQuery
      /// </summary>
      /// <param name="query">Sql Query</param>
      /// <returns>DbCommand</returns>
      public DbCommand GetSqlStringCommand(string query, IDictionary<string, object> parameterList)
      {
         DbCommand cmd = _database.GetSqlStringCommand(MapSql(query));
         if (parameterList == null || parameterList.Count == 0)
            return cmd;
         foreach (var parameter in parameterList)
         {
            AddInParameter(cmd, FormatParameter(parameter.Key)
                           , GetDbType(parameter.Value)
                           , parameter.Value ?? DBNull.Value);
         }
         return cmd;
      }

      /// <summary>
      /// GetCommandBy StoredProc
      /// </summary>
      /// <param name="storedProcedureName">storedProcedureName</param>
      /// <returns>DbCommand</returns>
      public DbCommand GetStoredProcCommand(string storedProcedureName)
      {
         return GetStoredProcCommand(MapSql(storedProcedureName));
      }

      public virtual DbCommand GetStoredProcCommand(string storedProcedureName, params object[] parameterValues)
      {
         return _database.GetStoredProcCommand(MapSql(storedProcedureName), parameterValues);
      }

      #endregion

      #region Private Mathod

      private static string MapSql(string sql)
      {
         return sql;
      }

      private string FormatParameter(string parameterName)
      {
         if (string.IsNullOrEmpty(parameterName))
            throw new ArgumentNullException("ParameterName can not be null");
         return parameterName.StartsWith("@") ? parameterName : parameterName.Insert(0, "@");
      }

      private DbType GetDbType(object parameterValue)
      {
         if (parameterValue is string) return DbType.String;
         if (parameterValue is double) return DbType.Double;
         if (parameterValue is DateTime) return DbType.DateTime;
         if (parameterValue is Int32) return DbType.Int32;
         return DbType.String;
      }

      #endregion

      //TODO:ExecuteByPage
   }
}